package yicr.untils;


import org.apache.poi.ss.usermodel.*;
import yicr.model.excel.ExcelStringRowTable;
import yicr.model.excel.ExcelTemplate;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

public class NslUtilExcelRead {


   public static String blankCellFillContent="&blank";
    public static String separator="_!_";


    public static ExcelStringRowTable readDataAsLineListOfStringList(ExcelTemplate excelTemplate, int beginColumnIndex, int endColumnIndex, boolean whetherIncludeHeadLines){
        try (InputStream inp = new FileInputStream(excelTemplate.getFileAbsPath())) {
//InputStream inp = new FileInputStream("workbook.xlsx");
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheet(excelTemplate.getSheetName());
            //计算公式值
            NslUtilExcelFormula.calculateFormulaOfSheet(sheet);
            //保存值
            NslUtilExcelFormula.formula2Value(sheet);

            int lastRowIndex=sheet.getLastRowNum();

            ExcelStringRowTable stringRowTable=new ExcelStringRowTable();

            ExcelStringRowTable.ExcelStringRow blankRow= buildBlankRowAsStringList(blankCellFillContent,beginColumnIndex,endColumnIndex);

            int beginRowIndex=0;
            if(!whetherIncludeHeadLines){
                beginRowIndex=excelTemplate.getHeaderRowCount();
            }

            for (int i = beginRowIndex; i <=lastRowIndex; i++) {
                Row row=sheet.getRow(i);
                ExcelStringRowTable.ExcelStringRow stringRow=new ExcelStringRowTable.ExcelStringRow();
                if(row==null){
                    stringRowTable.getRowList().add(blankRow);
                }else{
                    for (int j = beginColumnIndex; j <=endColumnIndex; j++) {
                        Cell cell=row.getCell(j);
                        String cellString;
                        if(cell==null){
                            cellString=blankCellFillContent;
                        }else{
                            cellString= NslUtilExcelCommonUtils.getCellValueByCell(wb,cell);
                        }
                        stringRow.getStringList().add(cellString);

                    }
                    stringRowTable.getRowList().add(stringRow);
                }
            }
            return stringRowTable;

        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 将表格中的除了表头以外的数据，每一行 组成一个string list，然后每行再组成List
     * @param excelTemplate
     * @param beginColumnIndex
     * @param endColumnIndex
     * @return
     */
    public static ExcelStringRowTable readDataAsLineListOfStringListExcludeHeadRows(ExcelTemplate excelTemplate, int beginColumnIndex, int endColumnIndex){
       return readDataAsLineListOfStringList(excelTemplate,beginColumnIndex,endColumnIndex,false);
    }


    public static ExcelStringRowTable readDataAsLineListOfStringListIncludeHeadRows(ExcelTemplate excelTemplate, int endColumnIndex){

        return readDataAsLineListOfStringList(excelTemplate,0,endColumnIndex,true);
    }

    /**
     * 将表格中的除了表头以外的数据，每一行 组成一个由分隔符组成的字符串，然后每行再组成List
     * 对于cell为空的，用 blankCellFillContent 填充
     * @param excelTemplate
     * @param beginColumnIndex
     * @param endColumnIndex
     * @param blankCellFillContent
     * @param separator
     * @return
     */
    public static List<String> readDataAsLineListOfOneString(ExcelTemplate excelTemplate, int beginColumnIndex, int endColumnIndex, String blankCellFillContent, String separator){
        try (InputStream inp = new FileInputStream(excelTemplate.getFileAbsPath())) {
//InputStream inp = new FileInputStream("workbook.xlsx");
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheet(excelTemplate.getSheetName());
            //计算公式值
            NslUtilExcelFormula.calculateFormulaOfSheet(sheet);
            //保存值
            NslUtilExcelFormula.formula2Value(sheet);

            int lastRowIndex=sheet.getLastRowNum();


//            String blankCellFillContent="&blank";
//            String separator="_!_";
            String blankRowString= buildBlankRowAsOneString(blankCellFillContent,separator,beginColumnIndex,endColumnIndex);

            List<String> lineStringList=new ArrayList<>();

            for (int i = excelTemplate.getHeaderRowCount(); i <=lastRowIndex; i++) {
                Row row=sheet.getRow(i);
                String lineString="";
                if(row==null){
                    lineStringList.add(blankRowString);
                }else{
                    for (int j = beginColumnIndex; j <=endColumnIndex; j++) {
                        Cell cell=row.getCell(j);
                        String cellString;
                        if(cell==null){
                            cellString=blankCellFillContent;
                        }else{
                            cellString= NslUtilExcelCommonUtils.getCellValueByCell(wb,cell);
                        }
                        if(j==endColumnIndex){
                            lineString+=cellString;
                        }else{
                            lineString+=cellString+separator;
                        }
                    }
                    lineStringList.add(lineString);
                }
            }

            return lineStringList;
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

    public static List<String> readDataAsLineListOfOneString(ExcelTemplate excelTemplate, int endColumnIndex){
        return readDataAsLineListOfOneString(excelTemplate,0,endColumnIndex,blankCellFillContent,separator);
    }


    private static String buildBlankRowAsOneString(String blankCellFillContent, String separator, int beginColumnIndex, int endColumnIndex){
        String blankRowString="";
        for (int i = beginColumnIndex; i <= endColumnIndex; i++) {
            blankRowString+=blankCellFillContent+separator;
        }
        blankRowString=blankRowString.substring(0,blankRowString.length()-separator.length());
        return blankRowString;
    }

    private static ExcelStringRowTable.ExcelStringRow buildBlankRowAsStringList(String blankCellFillContent, int beginColumnIndex, int endColumnIndex){
        ExcelStringRowTable.ExcelStringRow blankRow=new ExcelStringRowTable.ExcelStringRow();
        for (int i = beginColumnIndex; i <= endColumnIndex; i++) {
            blankRow.getStringList().add("");
        }

        return blankRow;
    }
}
